package com.xxl.job.executor.service.impl;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IMonitorInfoStatisGrantryService;


@Service("MonitorInfoStatisGrantryService")
public class MonitorInfoStatisGrantryService implements IMonitorInfoStatisGrantryService{

	 Connection connPre = null;
	 
	@Override
	public void transfer() throws Exception {
		// TODO Auto-generated method stub
		 XxlJobLogger.log("=====开始执行业务操作===");

	        try {
	            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
	            connPre = DataSourceUtils.getConnection(dataSourcePre);
	            connPre.setAutoCommit(false);
	            String recordStr = DBUtils.getCurDate(connPre);
	            String syncEndDate = recordStr + " 00:00:00";  
	            String syncStartDate = DBUtils.getTenDayAgoData(connPre,syncEndDate);           
	            syncEndDate =  recordStr + " 23:59:59";
	            
	            //清理10天的数据
	            clearNum(syncStartDate,syncEndDate,connPre);
	            //统计站上传落地省中心6-门架ETC交易量             
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,10);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,9);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,8);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,7);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,6);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,5);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,4);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,3);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,2);
	            connPre.commit();
	            monitorMainEtcGrantrytAll(syncStartDate,syncEndDate,connPre,1);
	            connPre.commit();
	            //统计站上传落地省中心7-门架mtc交易量           
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,10) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,9) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,8) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,7) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,6) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,5) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,4) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,3) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,2) ;
	            connPre.commit();
	            monitorMainMtcGrantrytAll( syncStartDate, syncEndDate, connPre,1) ;
	            
	            
	            connPre.commit();
	            XxlJobLogger.log("=====完成执行业务操作===");
	        }catch(Exception e){
	            if (connPre != null) {
	                try {
	                    connPre.rollback();
	                    XxlJobLogger.log("回滚成功.");
	                }catch (Exception e1){
	                    e1.printStackTrace();
	                }
	            }
	            XxlJobLogger.log("抛出异常："+e.getMessage());
	            throw e;
	        }finally{
	            if (connPre != null) {
	                try {
	                    connPre.setAutoCommit(true);
	                    connPre.close();
	                }catch (Exception e){
	                    e.printStackTrace();
	                }
	            }
	        }
	    }
	
    
	 /**
     * 清理需要修改的数据
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void clearNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " update TB_STATIS_MONITOR_MAIN set STATION_SEND_CNT=0,NOTDEAL_CNT=0 " +
    			"     where  STATIS_TYPE in ('6','7') " + 
    			"      and to_date(STATIS_DATE,'yyyy-mm-dd')>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  \r\n" + 
    			"      and  to_date(STATIS_DATE,'yyyy-mm-dd')<TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')  \r\n" ;
     	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]清理需要修改的数据数据(条):" + count);	
    }
	
	 /**
     * 统计站上传落地省中心6-门架ETC交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainEtcGrantrytAll(String startTime,String endTime,Connection conn,int day) throws SQLException {
    	int  daycha = day - 1;
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select substr(e.transtime,0,10) as statis_date,'6' as STATIS_TYPE, count(1) STATION_SEND_CNT\r\n" + 
    			"      from grantry_trans_main e\r\n" + 
    			"     where e.inserttabletime>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') + " + daycha + " \r\n" + 
    			"       and e.inserttabletime<= to_date('"+startTime+"','yyyy-MM-dd hh24:mi:ss')+3 + "+ day +"\r\n" + 
    			"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') +"+daycha+" \r\n" + 
    			"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')+"+day+" \r\n" + 
    			//"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')<  to_date('"+endTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and mediaType=1\r\n" + 
    			"       group by  substr(e.transtime,0,10)\r\n" + 
    			"	)  b\r\n" + 
    			"   on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"   WHEN MATCHED THEN\r\n" + 
    			"       update  set a.STATION_SEND_CNT = b.STATION_SEND_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"   WHEN not MATCHED THEN\r\n" + 
    			"      insert (STATIS_DATE,STATIS_TYPE,STATION_SEND_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"      values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_SEND_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]第"+day+"天站上传落地省中心6-门架ETC交易数据(条):" + count);	
 		
 		 sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select substr(e.transtime,0,10) as statis_date,'6' as STATIS_TYPE, count(1) NOTDEAL_CNT\r\n" + 
    			"      from grantry_trans_main_err e\r\n" + 
    			"     where e.inserttabletime>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')  + " + daycha + " \r\n" + 
    			"       and e.inserttabletime<= to_date('"+startTime+"','yyyy-MM-dd hh24:mi:ss')+3 + "+ day +"\r\n" + 
    			"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') +"+daycha+" \r\n" + 
    			"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')<  to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') +"+day+" \r\n" + 
    			"       and mediaType=1" +
    		    "       and haddeal=0     \r\n" + 
    			"       group by  substr(e.transtime,0,10)\r\n" + 
    			"	)  b\r\n" + 
    			"   on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"   WHEN MATCHED THEN\r\n" + 
    			"       update  set a.NOTDEAL_CNT = b.NOTDEAL_CNT,UPDATE_TIME=sysdate\r\n"+ 
    			"   WHEN not MATCHED THEN\r\n" + 
    			"      insert (STATIS_DATE,STATIS_TYPE,NOTDEAL_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"      values (b.STATIS_DATE,b.STATIS_TYPE,b.NOTDEAL_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
 		 count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]第"+day+"天站上传落地省中心6-门架ETC交易失败数据(条):" + count);	
 		
    }
    
    
    /**
     * 统计站上传落地省中心7-门架mtc交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainMtcGrantrytAll(String startTime,String endTime,Connection conn,int day) throws SQLException {
    	int  daycha = day - 1;
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select substr(e.transtime,0,10) as statis_date,'7' as STATIS_TYPE, count(1) STATION_SEND_CNT\r\n" + 
    			"      from grantry_trans_main e\r\n" + 
    			"     where e.inserttabletime>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') + "+daycha+"  \r\n" + 
    			"       and e.inserttabletime<= to_date('"+startTime+"','yyyy-MM-dd hh24:mi:ss')+3 + "+day+" \r\n" + 
    			"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') +"+daycha+" \r\n" +
    			"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')<  to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') +"+day+" \r\n" + 
    			"       and mediaType=2\r\n" + 
    			"       group by  substr(e.transtime,0,10)\r\n" + 
    			"	)  b\r\n" + 
    			"   on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"   WHEN MATCHED THEN\r\n" + 
    			"       update  set a.STATION_SEND_CNT = b.STATION_SEND_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"   WHEN not MATCHED THEN\r\n" + 
    			"      insert (STATIS_DATE,STATIS_TYPE,STATION_SEND_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"      values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_SEND_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]第"+day+"天站上传落地省中心7-门架MTC交易数据(条):" + count);	
 		
 		
 		 sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
     			"  using (\r\n" + 
     			"     select substr(e.transtime,0,10) as statis_date,'7' as STATIS_TYPE, count(1) NOTDEAL_CNT\r\n" + 
     			"      from grantry_trans_main_err e\r\n" + 
     			"     where e.inserttabletime>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') + " + daycha + " \r\n" + 
     			"       and e.inserttabletime<= to_date('"+startTime+"','yyyy-MM-dd hh24:mi:ss')+3+ "+day+" \r\n" + 
     			"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')>= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') + " + daycha + " \r\n" + 
     			"       and to_date(replace(e.transtime,'T',' '),'yyyy-MM-dd hh24:mi:ss')<  to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')+ "+day+" \r\n" + 
     			"       and mediaType=2" +
     		    "       and haddeal=0     \r\n" + 
     			"       group by  substr(e.transtime,0,10)\r\n" + 
     			"	)  b\r\n" + 
     			"   on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
     			"   WHEN MATCHED THEN\r\n" + 
     			"       update  set a.NOTDEAL_CNT = b.NOTDEAL_CNT,UPDATE_TIME=sysdate\r\n" +
     			"   WHEN not MATCHED THEN\r\n" + 
    			"      insert (STATIS_DATE,STATIS_TYPE,NOTDEAL_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n " + 
    			"      values (b.STATIS_DATE,b.STATIS_TYPE,b.NOTDEAL_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
  		 count = DBUtils.execute(conn, sql);
  		XxlJobLogger.log("[普通]第"+day+"天站上传落地省中心7-门架MTC交易失败数据(条):" + count);	  		
    }
    
	
}
